BigQuery の変更履歴を取得可能になりました（プレビュー）
ウィスキー、シガー、パイプをこよなく愛する大栗です。
本日はクラスメソッド株式会社の創立記念日である 7 月 7 日ということで皆がたくさんブログを書いているので、乗るしかない、このビッグウェーブにという事でブログネタを探していたら BigQuery の変更履歴が取得できるようになったというアップデートがあったのでご紹介します。
BigQuery の変更履歴は2022年7月7日現在において、プレビューのステータスです。このプロダクトまたは機能は、Google Cloud Platform の利用規約の一般提供前のサービス規約の対象となります。一般提供前のプロダクトと機能では、サポートが制限されることがあります。また、一般提供前のプロダクトや機能に変更が加えられると、他の一般提供前バージョンと互換性がない場合があります。詳細については、リリースステージの説明をご覧ください。
BigQuery の変更履歴
BigQuery の変更履歴は、BigQuery テーブルへの変更を追跡できるようになります。変更履歴はテーブル値関数（TVF）として表現され、指定した時間の範囲に行われた特定の変更を表示します。この機能により、テーブルに加えられた増分の変更を処理できます。変更内容を把握できるので、外部データストアの更新処理で全データ取得し洗い替えを行わずに変更されたデータだけを取得して更新することが可能になります。
APPENDS テーブル値関数（TVF）
APPENDSTVF は指定された時間の範囲でテーブルに追加されたすべての行のテーブルを返します。以下の操作はAPPENDSの変更履歴に行を追加します。
CREATE TABLEDDL 文INSERTDML 文MERGEDML 文- BigQuery へのデータロード
- ストリーミング取り込み
シンタックス
APPENDS( TABLE table, start_timestamp DEFAULT NULL, end_timestamp DEFAULT NULL)
table: BigQuery のテーブル名です。ビュー、サブクエリ、外部テーブル、マテリアライズド・ビュー、ワイルドカードテーブルは使用できません。start_timestamp: 変更が出力される最初の時刻を示すタイムスタンプ。NULL の場合はテーブル作成以降のすべての変更を返します。start_timestampの後にテーブルが作成された場合は実際のテーブル作成時刻が使用されます。タイムトラベルの範囲より早い時刻の場合はエラーを返します。通常のテーブルの場合は期間は 7 日ですが、タイムトラベル期間を短く設定できます。end_timestamp: 変更が出力される最新の時刻を示すタイムスタンプ。NULL の場合はクエリ開始までに行われたすべての変更を含みます。
APPENDSTVF は、クエリ実行時のテーブルのすべての列、_CHANGE_TYPE列（業を生成した変更の種類を示し、INSERTのみサポート）、_CHANGE_TIMESTAMP列（変更を行ったトランザクションのコジット時刻）を持つテーブルを返します。
制限
変更履歴には以下の制限があります。
- 更新や削除ではなく、追加に関する情報のみ表示します。
- データは、テーブルのタイムトラベル期間で制限されます。
やってみる
実際にテーブルを作成してレコードを挿入して、変更履歴を確認してみます。
まずテーブルを作成します。
$ bq query --use_legacy_sql=false \ 'CREATE TABLE dataset.sample_table2( col1 STRING, col2 STRING, col3 STRING);'
次にレコードを挿入します。
$ bq query --use_legacy_sql=false \
'INSERT INTO dataset.sample_table
VALUES
("10", "Guri / Hajime Oguri", "https://dev.classmethod.jp/author/oguri-hajime/"),
("20", "佐々木 大輔", "https://dev.classmethod.jp/author/sasaki-daisuke/");'
内容を確認します。
$ bq query --use_legacy_sql=false \ 'SELECT * FROM dataset.sample_table;' +------+---------------------+---------------------------------------------------+ | col1 | col2 | col3 | +------+---------------------+---------------------------------------------------+ | 20 | 佐々木 大輔 | https://dev.classmethod.jp/author/sasaki-daisuke/ | | 10 | Guri / Hajime Oguri | https://dev.classmethod.jp/author/oguri-hajime/ | +------+---------------------+---------------------------------------------------+
この状態で変更履歴を確認してみます。タイムスタンプ型の分解能はマイクロ秒ですが、記録されているデータとしてはミリ秒の模様です。
$ bq query --use_legacy_sql=false \
'SELECT col1
,col2
,col3
,_CHANGE_TYPE AS change_type
,CAST(_CHANGE_TIMESTAMP AS STRING FORMAT "YYYY-MM-DD HH24:MI:SS.FF6") AS change_timeFROM APPENDS
(TABLE dataset.sample_table, NULL, NULL
);'
+------+---------------------+---------------------------------------------------+-------------+----------------------------+
| col1 | col2 | col3 | change_type | change_time |
+------+---------------------+---------------------------------------------------+-------------+----------------------------+
| 20 | 佐々木 大輔 | https://dev.classmethod.jp/author/sasaki-daisuke/ | INSERT | 2022-07-07 09:11:04.558000 |
| 10 | Guri / Hajime Oguri | https://dev.classmethod.jp/author/oguri-hajime/ | INSERT | 2022-07-07 09:11:04.558000 |
+------+---------------------+---------------------------------------------------+-------------+----------------------------+
カラムを追加します。
$ bq query --use_legacy_sql=false \ 'ALTER TABLE dataset.sample_table ADD COLUMN col4 STRING;'
もう一行追加します。
$ bq query --use_legacy_sql=false \
'INSERT INTO dataset.sample_table
VALUES
("30", "横田 あかり", "https://dev.classmethod.jp/author/akari7/", "329");'
レコードを更新します。
$ bq query --use_legacy_sql=false \ 'UPDATE dataset.sample_table SET col2 = "大栗 宗" WHERE col1 = "10";'
レコードを削除します。
$ bq query --use_legacy_sql=false \ 'DELETE mydataset.sample_table WHERE col1 = "20";'
この状態の内容を確認します。
$ bq query --use_legacy_sql=false \ 'SELECT * FROM dataset.sample_table;' +------+-------------+-------------------------------------------------+------+ | col1 | col2 | col3 | col4 | +------+-------------+-------------------------------------------------+------+ | 30 | 横田 あかり | https://dev.classmethod.jp/author/akari7/ | 329 | | 10 | 大栗 宗 | https://dev.classmethod.jp/author/oguri-hajime/ | NULL | +------+-------------+-------------------------------------------------+------+
変更履歴を確認します。レコードの挿入、更新、削除を行いましたが、変更履歴では挿入のみが出力されます。col1="10"のレコードで col2 を大栗 宗へ変更しましたが、変更履歴では変わらないので注意が必要です。
$ bq query --use_legacy_sql=false \
'SELECT col1
,col2
,col3
,_CHANGE_TYPE AS change_type
,CAST(_CHANGE_TIMESTAMP AS STRING FORMAT "YYYY-MM-DD HH24:MI:SS.FF6") AS change_timeFROM APPENDS
(TABLE dataset.sample_table, NULL, NULL
);'
+------+---------------------+---------------------------------------------------+------+-------------+----------------------------+
| col1 | col2 | col3 | col4 | change_type | change_time |
+------+---------------------+---------------------------------------------------+------+-------------+----------------------------+
| 30 | 横田 あかり | https://dev.classmethod.jp/author/akari7/ | 329 | INSERT | 2022-07-07 09:33:40.009000 |
| 20 | 佐々木 大輔 | https://dev.classmethod.jp/author/sasaki-daisuke/ | NULL | INSERT | 2022-07-07 09:11:04.558000 |
| 10 | Guri / Hajime Oguri | https://dev.classmethod.jp/author/oguri-hajime/ | NULL | INSERT | 2022-07-07 09:11:04.558000 |
+------+---------------------+---------------------------------------------------+------+-------------+----------------------------+
この様に BigQuery の変更履歴を取得できました。
さいごに
会社の創立記念日ということでブログネタを探していましたが、BigQuery の重要そうなアップデートがあったので早速試しました。変更履歴がテーブルとして取得できるため、一般的な ETL/ELT ツールでデータを取得でき別のデータストアへデータ連携するときに差分だけ取得することも可能になりそうです。更新や削除は記録されないので注意が必要ですが、活用の幅が広そうな機能なので是非試して頂ければと思います。
